from code.data.Notice import Notice
from code.dao.user_management import __check_user_exist
from code.dao.config import mysql_conn, cursor

import logging
import pymysql
import traceback
import datetime


def add_notice(notice: Notice):
    if notice.userID is None:
        logging.error("UserID is required.")
        return False
    if not __check_user_exist(notice.userID):
        return False
    if notice.type is None:
        logging.error("Type is required.")
        return False
    if notice.priority is None:
        logging.error("Priority is required.")
        return False
    if notice.content is None:
        logging.error("Content is required.")
        return False
    sql = "INSERT INTO notice(type, priority, content, userID) VALUES ('{}', {}, '{}', '{}')".format(
        notice.type, notice.priority, notice.content, notice.userID)
    try:
        cursor.execute(sql)
        mysql_conn.commit()
    except pymysql.Error as e:
        logging.error("Inserting notice into database failed: " + str(e.args[1]))
        mysql_conn.rollback()
        return False
    logging.info("Notice is added with SQL: " + sql)
    return True


def search_notice(notice: Notice, search_by: str = "all", fuzzy_search: bool = False):
    notices = []
    if search_by == "all":
        sql = "SELECT * FROM notice"
    elif search_by == "noticeID":
        if notice.noticeID is None:
            logging.error("NoticeID is required.")
            return None
        sql = "SELECT * FROM notice WHERE noticeID = {}".format(notice.noticeID)
    elif search_by == "type":
        if notice.type is None:
            logging.error("Type is required.")
            return None
        sql = "SELECT * FROM notice WHERE type = '{}'".format(notice.type)
    elif search_by == "time":
        if notice.time is None:
            logging.error("Time is required.")
            return None
        sql = "SELECT * FROM notice WHERE time = '{}'".format(notice.time)
        if fuzzy_search:
            sql = "SELECT * FROM notice WHERE time LIKE '%{}%'".format(notice.time)
    elif search_by == "priority":
        if notice.priority is None:
            logging.error("Priority is required.")
            return None
        sql = "SELECT * FROM notice WHERE priority = {}".format(notice.priority)
    elif search_by == "content":
        if notice.content is None:
            logging.error("Content is required.")
            return None
        sql = "SELECT * FROM notice WHERE content = '{}'".format(notice.content)
        if fuzzy_search:
            sql = "SELECT * FROM notice WHERE content LIKE '%{}%'".format(notice.content)
    elif search_by == "userID":
        if notice.userID is None:
            logging.error("UserID is required.")
            return None
        sql = "SELECT * FROM notice WHERE userID = '{}'".format(notice.userID)
    else:
        logging.error("Attribute for search_by is not exist, use one in "
                      '["all", "noticeID", "type", "time", "priority", "content", "userID"]')
        return None
    try:
        cursor.execute(sql)
        results = cursor.fetchall()
        if not results:
            logging.warning("Notice is not exist.")
            return None
    except pymysql.Error as e:
        logging.error("Searching for notice failed: " + str(e.args[1]))
        traceback.print_exc()
        return None
    for result in results:
        notices.append(Notice(result[0], result[1], result[2], result[3], result[4], result[5]))
    logging.info("Search notice(s) with SQL: {}".format(sql))
    return notices


def update_notice(notice: Notice, update_by: str):
    if notice.noticeID is None:
        logging.error("NoticeID is required.")
        return False
    if not __check_notice_exist(notice.noticeID):
        return False
    if update_by == "priority":
        if notice.priority is None:
            logging.error("Priority is required.")
            return False
        sql = "UPDATE notice SET priority = {} WHERE noticeID = {}".format(notice.priority, notice.noticeID)
    else:
        logging.error("Attribute for update_by is not exist, use one in "
                      '["priority"]')
        return False
    try:
        cursor.execute(sql)
        mysql_conn.commit()
    except pymysql.Error as e:
        logging.error("Update notice from database failed: " + str(e.args[1]))
        mysql_conn.rollback()
        return False
    logging.info("Notice is updated with SQL: {}".format(sql))
    return True


def delete_notice(notice: Notice):
    if notice.noticeID is None:
        logging.error("NoticeID is required.")
        return False
    if not __check_notice_exist(notice.noticeID):
        return False
    sql = "DELETE FROM notice WHERE noticeID = {}".format(notice.noticeID)
    try:
        cursor.execute(sql)
        mysql_conn.commit()
    except pymysql.Error as e:
        logging.error("Delete notice from database failed: " + str(e.args[1]))
        mysql_conn.rollback()
        return False
    logging.info("Notice is deleted with SQL: {}".format(sql))
    return True


def __check_notice_exist(noticeID: int):
    sql = "SELECT * FROM notice WHERE noticeID = {}".format(noticeID)
    try:
        cursor.execute(sql)
        results = cursor.fetchall()
        if not results:
            logging.error("Notice {} is not exist".format(noticeID))
            return False
    except pymysql.Error as e:
        logging.error("Searching for notice failed: " + str(e.args[1]))
        traceback.print_exc()
        return False
    return True


if __name__ == "__main__":
    notice1 = Notice(type="homework", priority=0, content="complete SE(admin)", userID="admin")
    notice2 = Notice(type="homework", priority=0, content="complete SE(fake user)", userID="fake user")
    notice3 = Notice(type="homework", priority=1, content="complete SE(admin & priority = 1)", userID="admin")
    notice4 = Notice(type="normal", priority=0, content="complete SE(xqk & normal)", userID="2019300003077")
    print(add_notice(notice1))
    print(add_notice(notice2))
    print(add_notice(notice3))
    print(add_notice(notice4))
    results1 = search_notice(notice1, "all")
    results2 = search_notice(Notice(content="SE"), "content")
    results3 = search_notice(Notice(content="SE"), "content", True)
    results4 = search_notice(notice1, "fake")
    results5 = search_notice(Notice(time=datetime.date.today()), "time")
    results6 = search_notice(Notice(time=datetime.date.today()), "time", True)
    print(results1)
    for result in results1:
        print(result.to_string())
    print(results2)
    print(results3)
    for result in results3:
        print(result.to_string())
    print(results4)
    print(results5)
    print(results6)
    for result in results6:
        print(result.to_string())
    print(update_notice(Notice(noticeID=1, priority=1), "priority"))
    results = search_notice(Notice(), "all")
    for result in results:
        print(result.to_string())
    print(delete_notice(Notice(noticeID=1)))
    print(delete_notice(Notice(noticeID=3)))
